# -*- coding:utf-8 -*-

#pip install sqlalchemy

import sqlalchemy

print sqlalchemy.__version__




from sqlalchemy import create_engine, MetaData,\
        Table, Column, Integer, String, ForeignKey


import os
curDir = os.getcwd()
print curDir    #/home/ding/PycharmProjects/SQLAlchemyTest/test

file = curDir + '/foo.db'

engine = create_engine('sqlite:///' + file, echo = False)  # create_engine('sqlite:///D:/mycode/SQLAlchemyTest/test/foo.db', echo = False)
metadata = MetaData(engine)



user_table = Table('user', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('fullname', String(100))
        )

address_table = Table('address', metadata,
        Column('id', Integer, primary_key=True),
        Column('user_id', None, ForeignKey('user.id')),
        Column('email', String(128), nullable=False)
        )

#metadata.create_all()


user_table.create(checkfirst=True)
address_table.create(checkfirst=True)



#user_table = Table('user', metadata, autoload=True)
address_table = Table('address', metadata, autoload=True)   #如果被反射的表外键引用了另一个表，那么被引用的表也会一并被反射。

print 'user' in metadata.tables
user_table = metadata.tables['user']
print [c.name for c in user_table.columns]

print 'address' in metadata.tables
print [c.name for c in address_table.columns]



ins = user_table.insert()
print ins

conn = engine.connect()

resultProxy=engine.execute('select * from user')
for row in resultProxy.fetchall():
    print row

result = conn.execute(ins, name='adam', fullname='Adam Gu')


conn.execute(address_table.insert(), [
    { 'user_id': 1, 'email': 'sprinfall@gmail.com' },
    { 'user_id': 1, 'email': 'sprinfall@hotmail.com' },
    ])


